ALTER      proc sp_create_inventory_lot (

@process_step_id int, 
@location_id int,
@lot_number varchar(50), 
@qty_in int,
@date_in varchar (20),
@tested_qty int,
@rejected_qty int,
@dateCode varchar(50))

as
begin

	if @date_in = '01/01/1900' 
	begin
		set @date_in = null
	end

	--if @date_out = '01/01/1900' 
	--begin
		--set @date_out = null
	--end
	--if @qty_out = 0
	--begin
		--set @qty_out = null
	--end

	declare @lot_current_status varchar(50)
	declare @normal_cost float
	declare @lot_id int

	Select @lot_current_status = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Entered_By, Lot_Date_Code ) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, 0, @dateCode )
		select @lot_id = @@IDENTITY				
	end
	
	else 
	begin
		update Lot_Table set Lot_Current_Status = @lot_current_status, Lot_Date_Code = @dateCode where Lot_ID = @lot_id
		return
	end

	-- insert/update Inventory Lot
	declare @inv_lot_id int
	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
	if @inv_lot_id is null
	begin
		Insert into Inventory_Lot ( Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, IL_Test_Qty, IL_Rejected_Qty ) values (@lot_id, @qty_in, NULL, @date_in, NULL, @tested_qty, @rejected_qty)
	end
	else
	begin
		-- dummy
		update Inventory_Lot set IL_Qty_In = @qty_in, IL_Test_Qty = @tested_qty, IL_Rejected_Qty = @rejected_qty where Lot_ID = @inv_lot_id
	end
end
